﻿#region Disclaimer/Info
///////////////////////////////////////////////////////////////////////////////////////////////////
// MetaCollection
// 
// MetaCollection is a system to provide a standard structure, permissions and metadata service
// for content organization.
//
// This project is hosted at Google Code at http://code.google.com/p/metacollection/
//
// This project is licensed under the BSD license.  See the License.txt file for more information.
///////////////////////////////////////////////////////////////////////////////////////////////////
#endregion

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Transactions;
using System.Web;
using MetaCollection.Framework.Change;
using MetaCollection.Framework.Model;

namespace MetaCollection.Framework.Repository
{
	public class AdoNetItem : IItemRepository
	{
		private string m_ConnectionString;
		private bool m_Initialized;

		/// <summary>
		/// Ensures the repository has been initialized.
		/// </summary>
		/// <exception cref="InvalidOperationException">Thrown if <see cref="AdoNetItem.Initialize"/> has not been called.</exception>
		private void EnsureInitialized()
		{
			if (!m_Initialized)
				throw new InvalidOperationException("Resource Provider must be initialized prior to use.");
		}


		#region IItemRepository Members

		/// <summary>
		/// Initializes a new instance of the IItemRepository
		/// </summary>
		/// <param name="configData"></param>
		public void Initialize(string configData)
		{
			m_ConnectionString = configData;
			m_Initialized = true;
		}


		/// <summary>
		/// Gets all versions of an item
		/// </summary>
		/// <param name="id">The id of the item.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns>
		/// An ItemInstance for the given Item Id or null if no object found
		/// </returns>
		public IEnumerable<IItemInstance> GetItem(Guid id, LoadOptions opt)
		{
			EnsureInitialized();

			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id", "Item id cannot be null or empty.");

			if (opt == LoadOptions.None)
				throw new ArgumentException("GetItem cannot be call with LoadOption set to None", "opt");

			string query = String.Format(CultureInfo.InvariantCulture, Resources.AdoNetItemGetItemSql, GetSelectColumns(opt));
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("@id", id);
			return GetItemsFromDatabase(query, opt, parameters);
		}

		/// <summary>
		/// Finds an Item by Id-Version.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <param name="version">The version.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns>
		/// An ItemInstance for the given Item Id-Version or null if no object found
		/// </returns>
		public IItemInstance GetItem(Guid id, int version, LoadOptions opt)
		{
			EnsureInitialized();

			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id", "Item id cannot be null or empty.");

			if (opt == LoadOptions.None)
				throw new ArgumentException("GetItem cannot be call with an invalid item version", "opt");

			if (version < 1)
				throw new ArgumentException("GetItem cannot be call with LoadOption set to None", "version");

			string query = String.Format(CultureInfo.InvariantCulture, Resources.AdoNetItemGetItemVersionSql, GetSelectColumns(opt));
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("@id", id);
			parameters.Add("@version", version);
			return GetItemsFromDatabase(query, opt, parameters).FirstOrDefault();
		}

		/// <summary>
		/// Finds an item by name with a given parent.
		/// </summary>
		/// <param name="parentId">The parent id.</param>
		/// <param name="name">The name.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns>an ItemInstance</returns>
		/// <remarks>Returns the published version ?? max(version) of each item</remarks>
		public IEnumerable<IItemInstance> FindByName(Guid parentId, string name, LoadOptions opt)
		{
			EnsureInitialized();

			if (parentId == Guid.Empty || parentId == null)
				throw new ArgumentNullException("parentId", "Parent id cannot be null or empty.");

			if (opt == LoadOptions.None)
				throw new ArgumentException("FindByName cannot be call with LoadOption set to None", "opt");

			if (string.IsNullOrEmpty(name))
				throw new ArgumentNullException("name", "name cannot be null or empty.");

			string query = string.Format(CultureInfo.InstalledUICulture, Resources.AdoNetItemFindByNameSql, GetSelectColumns(opt));
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("@parentId", parentId);
			parameters.Add("@name", name);
			return GetItemsFromDatabase(query, opt, parameters);
		}
		/// <summary>
		/// Finds all the items with a given parent.
		/// </summary>
		/// <param name="parentId">The parent id.</param>
		/// <param name="includeIndirect">if set to <c>true</c> the children that are not directly under the parent are also included.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns></returns>
		/// <remarks>Returns the published version ?? max(version) of each item</remarks>
		public IEnumerable<IItemInstance> FindByParent(Guid parentId, bool includeIndirect, LoadOptions opt)
		{
			EnsureInitialized();

			if (parentId == Guid.Empty || parentId == null)
				throw new ArgumentNullException("parentId", "Parent id cannot be null or empty.");

			if (opt == LoadOptions.None)
				throw new ArgumentException("FindByParent cannot be call with LoadOption set to None", "opt");


			string query = string.Format(CultureInfo.InstalledUICulture, includeIndirect ? Resources.AdoNetItemFindByParentHrefSql : Resources.AdoNetItemFindByParentSql, GetSelectColumns(opt));
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("@parentId", parentId);
			return GetItemsFromDatabase(query, opt, parameters);
		}

		public IEnumerable<IItemInstance> GetItemsFromDatabase(string commandText, LoadOptions opt, Dictionary<string, object> parameters)
		{
			using (SqlConnection connection = new SqlConnection(m_ConnectionString))
			{
				using (SqlCommand cmd = new SqlCommand(commandText, connection))
				{
					cmd.CommandText = commandText;
					foreach (KeyValuePair<string, object> parameter in parameters)
					{
						cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
					}
					connection.Open();
					using (SqlDataReader reader = cmd.ExecuteReader())
					{
						do
						{
							if (!reader.HasRows)
								yield break;

							while (reader.Read())
							{
								AdoItemInstance newItem = new AdoItemInstance();
								for (int i = 0; i < reader.FieldCount; i++)
								{
									MapItemProperty(reader, newItem, i);
								}
								if (opt.IsSet(LoadOptions.AccessClaims))
									newItem.AccessClaims = FillItemAccessClaim(newItem, opt);
								if (opt.IsSet(LoadOptions.Relationships))
									newItem.Relationships = FillItemRelationship(newItem, opt);
								yield return newItem;
							}
						} while (reader.NextResult());
					}
				}
				connection.Close();
			}
		}

		/// <summary>
		/// Puts the item into persistant storage or updates an existing item.
		/// </summary>
		/// <param name="item"></param>
		/// <param name="changeCollection"></param>
		/// <param name="requestType"></param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1002:DoNotExposeGenericLists")]
		public IItemInstance SaveItem(IItemInstance item, ItemChangeCollection changeCollection) 
		{
			EnsureInitialized();

			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null.");

			if (changeCollection == null)
				throw new ArgumentNullException("changeCollection", "Change Collection cannot be null.");

			//if (!requestType.HasType(RequestTypes.SetItem))
			//     throw new ArgumentException("Save must contain request type of setitem.", "requestType");

			if (changeCollection.ChangeType.HasType(ItemChangeTypes.Create))
			{
				CreateItem(item, changeCollection.Options);
			}
			else if (changeCollection.Count > 0)
			{
				UpdateItemInstance(item, changeCollection);			
			}
			return item;
		}

		public void RemoveItem(Guid id)
		{
			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id");

			EnsureInitialized();

			using (TransactionScope trans = new TransactionScope())
			{
				DeleteItemRelationships(id);
				DeleteItemVersions(id);
				DeleteItem(id);
				trans.Complete();
			}
		}

		public void RemoveItem(Guid id, int version)
		{
			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id");

			if (!(version >= 1))
				throw new ArgumentNullException("version");

			EnsureInitialized();

			using (TransactionScope trans = new TransactionScope())
			{
				IEnumerable<IItemInstance> items = GetItem(id, LoadOptions.ItemReference | LoadOptions.Version);
				bool otherVersionsExist = items.FirstOrDefault(p => p.Version != version) != null;
				DeleteItemRelationship(id, version);
				DeleteItemVersion(id, version);

				if (!otherVersionsExist)
				{
					DeleteItem(id);
				}
				trans.Complete();
			}
		}

		#endregion

		#region ItemRelationship Methods

		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private IEnumerable<IItemRelationship> FillItemRelationship(IItemInstance item, LoadOptions opt)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (opt == LoadOptions.None)
				throw new ArgumentException("Load Options can not be set to none", "opt");

			using (SqlConnection connection = new SqlConnection(m_ConnectionString))
			{
				if (!opt.IsSet(LoadOptions.Relationships))
					yield break;

				if (!opt.IsSet(LoadOptions.Version | LoadOptions.Id))
					throw new ArgumentException("Loading Item Relationships requires that Item Id and Item Version are loaded as well.", "opt");

				connection.Open();
				using (SqlCommand readData = new SqlCommand(Resources.AdoNetItemFillItemRelationshipSql, connection))
				{
					readData.Parameters.Clear();
					readData.Parameters.AddWithValue("@id", item.Id);
					readData.Parameters.AddWithValue("@version", item.Version);
					SqlDataReader reader = readData.ExecuteReader();
					while (reader.Read())
					{
						ItemRelationship relation = new ItemRelationship();
						relation.State = reader.IsDBNull(reader.GetOrdinal("State")) ? null : reader.GetString(reader.GetOrdinal("State"));
						relation.TargetItem = new Uri(reader.GetString(reader.GetOrdinal("TargetItem")));
						relation.RelationshipType = reader.GetString(reader.GetOrdinal("RelationshipType"));
						yield return relation;
					}
					reader.Close();
				}
				connection.Close();
			}
		}

		/// <summary>
		/// Inserts the item relationships.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="options">The options of which values in the item to set.</param>
		private void InsertItemRelationships(IItemInstance item, LoadOptions options)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (options == LoadOptions.None)
				throw new ArgumentException("Load options can not be set to none", "options");

			if (!options.IsSet(LoadOptions.Relationships))
				return;

			using (DataTable dt = new DataTable())
			{
				dt.Locale = CultureInfo.InvariantCulture;
				dt.Columns.Add("ItemId", typeof(Guid));
				dt.Columns.Add("Version", typeof(int));
				dt.Columns.Add("RelationshipType", typeof(string));
				dt.Columns.Add("TargetItem", typeof(string));
				dt.Columns.Add("State", typeof(string));

				foreach (IItemRelationship relationship in item.Relationships)
				{
					dt.Rows.Add(new object[] { item.Id, item.Version, relationship.RelationshipType, relationship.TargetItem, string.IsNullOrEmpty(relationship.State) ? null : relationship.State });
				}

				using (SqlBulkCopy b = new SqlBulkCopy(m_ConnectionString))
				{
					b.DestinationTableName = "structure.ItemRelationship";
					b.BatchSize = item.Relationships.Count();
					b.WriteToServer(dt);
				}
			}
		}

		/// <summary>
		/// Inserts the item relationship.
		/// </summary>
		/// <param name="itemId">The item id.</param>
		/// <param name="itemVersion">The item version.</param>
		/// <param name="relationshipType">Type of the relationship.</param>
		/// <param name="targetItem">The target item.</param>
		/// <param name="state">The state.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void InsertItemRelationship(Guid itemId, int itemVersion, string relationshipType, Uri targetItem, string state)
		{
			if (itemId == Guid.Empty || itemId == null)
				throw new ArgumentNullException("itemId", "Itemid cannot be null or empty.");

			if (itemVersion < 1)
				throw new ArgumentException("Item Version must be greater than 0", "itemVersion");

			if (string.IsNullOrEmpty(relationshipType))
				throw new ArgumentNullException("relationshipType");

			if (targetItem == null)
				throw new ArgumentNullException("targetItem");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemInsertItemRelationshipSql, sqlConn))
				{
					writeData.Parameters.AddWithValue("@ItemId", itemId);
					writeData.Parameters.AddWithValue("@Version", itemVersion);
					writeData.Parameters.AddWithValue("@RelationshipType", relationshipType);
					writeData.Parameters.AddWithValue("@TargetItem", targetItem.ToString());
					writeData.Parameters.AddWithValue("@State", string.IsNullOrEmpty(state) ? DBNull.Value : (object)state);
					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Updates the item relationship.
		/// </summary>
		/// <param name="itemId">The item id.</param>
		/// <param name="itemVersion">The item version.</param>
		/// <param name="relationshipType">Type of the relationship.</param>
		/// <param name="targetItem">The target item.</param>
		/// <param name="state">The state.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void UpdateItemRelationship(Guid itemId, int itemVersion, string relationshipType, Uri targetItem, string state)
		{
			if (itemId == Guid.Empty || itemId == null)
				throw new ArgumentNullException("itemId", "Item id cannot be null or empty.");

			if (itemVersion < 1)
				throw new ArgumentException("Item Version must be greater than 0", "itemVersion");

			if (string.IsNullOrEmpty(relationshipType))
				throw new ArgumentNullException("relationshipType", "Relationship type is required and can not be null or empty.");

			if (targetItem == null)
				throw new ArgumentNullException("targetItem");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemUpdateItemRelationshipSql, sqlConn))
				{
					writeData.Parameters.AddWithValue("@ItemId", itemId);
					writeData.Parameters.AddWithValue("@Version", itemVersion);
					writeData.Parameters.AddWithValue("@RelationshipType", relationshipType);
					writeData.Parameters.AddWithValue("@TargetItem", targetItem.ToString());
					writeData.Parameters.AddWithValue("@State", string.IsNullOrEmpty(state) ? DBNull.Value : (object)state);
					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Deletes the item relationship.
		/// </summary>
		/// <param name="itemId">The item id.</param>
		/// <param name="itemVersion">The item version.</param>
		/// <param name="relationshipType">Type of the relationship.</param>
		/// <param name="targetItem">The target item.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void DeleteItemRelationship(Guid itemId, int itemVersion, string relationshipType, Uri targetItem)
		{
			if (itemId == Guid.Empty || itemId == null)
				throw new ArgumentNullException("itemId", "Itemid cannot be null or empty.");

			if (itemVersion < 1)
				throw new ArgumentException("Item Version must be greater than 0.", "itemVersion");

			if (string.IsNullOrEmpty(relationshipType))
				throw new ArgumentNullException("relationshipType");

			if (targetItem == null)
				throw new ArgumentNullException("targetItem");

			//KeyValuePair<Guid, string> type = GetRelationshipTypes().FirstOrDefault(p => String.Equals(p.Value, relationshipType, StringComparison.OrdinalIgnoreCase));
			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemDeleteItemRelationshipSql, sqlConn))
				{
					writeData.Parameters.AddWithValue("@ItemId", itemId);
					writeData.Parameters.AddWithValue("@Version", itemVersion);
					writeData.Parameters.AddWithValue("@RelationshipType", relationshipType);
					writeData.Parameters.AddWithValue("@TargetItem", targetItem.ToString());
					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Deletes the item relationship.
		/// </summary>
		/// <param name="itemId">The item id.</param>
		/// <param name="itemVersion">The item version.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void DeleteItemRelationship(Guid itemId, int itemVersion)
		{
			if (itemId == Guid.Empty || itemId == null)
				throw new ArgumentNullException("itemId", "Itemid cannot be null or empty.");

			if (itemVersion < 1)
				throw new ArgumentException("Item Version must be greater than 0", "itemVersion");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemDeleteItemRelationshipByVersionSql, sqlConn))
				{
					writeData.Parameters.AddWithValue("@ItemId", itemId);
					writeData.Parameters.AddWithValue("@Version", itemVersion);
					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Deletes the item relationship.
		/// </summary>
		/// <param name="itemId">The item id.</param>
		/// <param name="itemVersion">The item version.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void DeleteItemRelationships(Guid itemId)
		{
			if (itemId == Guid.Empty || itemId == null)
				throw new ArgumentNullException("itemId", "Itemid cannot be null or empty.");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemDeleteItemRelationshipByIdSql, sqlConn))
				{
					writeData.Parameters.AddWithValue("@ItemId", itemId);
					writeData.ExecuteNonQuery();
				}
			}
		}

		#endregion

		#region ItemClaim Methods

		private IEnumerable<IItemClaim> FillItemAccessClaim(IItemInstance item, LoadOptions opt)
		{
			if (item == null)
				throw new ArgumentNullException("Item cannot be null");

			if (opt == LoadOptions.None)
				throw new ArgumentNullException("opt");

			using (SqlConnection connection = new SqlConnection(m_ConnectionString))
			{
				if (!opt.IsSet(LoadOptions.AccessClaims))
					yield break;

				if (!opt.IsSet(LoadOptions.Id))
					throw new ArgumentException("Loading Item AccessClaims requires that Item Id to be loaded.", "opt");
				connection.Open();
				using (SqlCommand readData = new SqlCommand("SELECT Id, ClaimType, ClaimValue, AccessFlags FROM structure.ItemClaim WHERE Id = @id", connection))
				{
					readData.Parameters.Clear();
					readData.Parameters.AddWithValue("@id", item.ItemClaimId);
					SqlDataReader reader = readData.ExecuteReader(CommandBehavior.SingleResult);
					while (reader.Read())
					{
						ItemClaim claim = new ItemClaim();
						claim.Id = reader.GetGuid(reader.GetOrdinal("Id"));
						claim.ClaimType = reader.GetString(reader.GetOrdinal("ClaimType"));
						claim.ClaimValue = reader.GetString(reader.GetOrdinal("ClaimValue"));
						claim.Rights = (AccessRights)reader.GetInt32(reader.GetOrdinal("AccessFlags"));
						yield return claim;
					}
					reader.Close();
				}
				connection.Close();
			}
		}

		/// <summary>
		/// Inserts the item claim.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <param name="claimType">Type of the claim.</param>
		/// <param name="claimValue">The claim value.</param>
		/// <param name="accessFlags">The access flags.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void InsertItemClaim(Guid id, string claimType, string claimValue, AccessRights accessFlags)
		{
			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id", "Item Id cannot be null or Empty");

			if (string.IsNullOrEmpty(claimType))
				throw new ArgumentNullException("claimType", "Claim type cannot be null or empty");

			if (string.IsNullOrEmpty(claimValue))
				throw new ArgumentNullException("claimValue", "Claim value cannot be null or empty");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemInsertItemClaim, sqlConn))
				{
					writeData.Parameters.AddWithValue("@Id", id);
					writeData.Parameters.AddWithValue("@ClaimType", claimType);
					writeData.Parameters.AddWithValue("@ClaimValue", claimValue);
					writeData.Parameters.AddWithValue("@AccessFlags", (int)accessFlags);
					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Update the item claim.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <param name="claimType">Type of the claim.</param>
		/// <param name="claimValue">The claim value.</param>
		/// <param name="accessFlags">The access flags.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void UpdateItemClaim(Guid id, string claimType, string claimValue, AccessRights accessFlags)
		{
			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id", "Item Id cannot be null or Empty");

			if (string.IsNullOrEmpty(claimType))
				throw new ArgumentNullException("claimType", "Claim type cannot be null or empty");

			if (string.IsNullOrEmpty(claimValue))
				throw new ArgumentNullException("claimValue", "Claim value cannot be null or empty");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemUpdateItemClaim, sqlConn))
				{
					writeData.Parameters.AddWithValue("@Id", id);
					writeData.Parameters.AddWithValue("@ClaimType", claimType);
					writeData.Parameters.AddWithValue("@ClaimValue", claimValue);
					writeData.Parameters.AddWithValue("@AccessFlags", (int)accessFlags);
					writeData.ExecuteNonQuery();
				}
				sqlConn.Close();
			}
		}

		/// <summary>
		/// Deletes the item claim.
		/// </summary>
		/// <param name="itemId">The item id.</param>
		/// <param name="claimType">Type of the claim.</param>
		/// <param name="claimValue">The claim value.</param>
		private void DeleteItemClaimBy3Keys(Guid itemId, string claimType, string claimValue)
		{
			if (itemId == Guid.Empty || itemId == null)
				throw new ArgumentNullException("itemId", "Item Id cannot be null or Empty");

			if (string.IsNullOrEmpty(claimType))
				throw new ArgumentNullException("claimType", "claimtype cannot be null or empty");

			if (string.IsNullOrEmpty(claimValue))
				throw new ArgumentNullException("claimValue", "claimvalue cannot be null or empty");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(String.Format(CultureInfo.InvariantCulture, "DELETE FROM [structure].[ItemClaim] WHERE Id = @Id AND ClaimType = @claimType AND ClaimValue = @claimValue"), sqlConn))
				{
					writeData.Parameters.AddWithValue("@Id", itemId);
					writeData.Parameters.AddWithValue("@claimType", claimType);
					writeData.Parameters.AddWithValue("@claimValue", claimValue);
					writeData.ExecuteNonQuery();
				}
			}
		}

		#endregion

		# region Item Methods

		/// <summary>
		/// Creates the item.
		/// </summary>
		/// <param name="item">The item.</param>
		private void CreateItem(IItemInstance item, LoadOptions opt)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (opt == LoadOptions.None)
				throw new ArgumentNullException("opt", "LoadOptions cannot be set to none");

			using (TransactionScope trans = new TransactionScope())
			{
				if (item.ParentId == null || item.ParentId == Guid.Empty)
				{
					foreach (IItemClaim claim in item.AccessClaims)
					{
						if (item.ItemClaimId == claim.Id)
						{
							InsertItemClaim(claim.Id, claim.ClaimType, claim.ClaimValue, claim.Rights);
						}
					}
				}
				InsertItem(item, opt);
				item.Version = InsertItemVersion(item, opt);
				InsertItemRelationships(item, opt);

				using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
				{
					sqlConn.Open();
					using (SqlCommand sqlCom = new SqlCommand("UPDATE structure.Item SET Href = structure.getItemPath(@id, 1) where id = @id", sqlConn))
					{
						sqlCom.Parameters.AddWithValue("@id", item.Id);
						sqlCom.ExecuteNonQuery();
					}
				}
				trans.Complete();
			}
		}

		/// <summary>
		/// Inserts the item.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="opt">The options of which fields should be set.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void InsertItem(IItemInstance item, LoadOptions opt)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (opt == LoadOptions.None)
				throw new ArgumentException("LoadOptions cannot be set to none", "opt");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemInsertItemSql, sqlConn))
				{
					// new items may not have an id assigned at this point.
					if (item.Id == null || item.Id == Guid.Empty)
					{
						item.Id = Guid.NewGuid();
						item.Version = 1; // default to 1 for new items.
					}

					if (item.ParentId == Guid.Empty && item.ItemClaimId == Guid.Empty)
						throw new InvalidOperationException("If Parent Id is Null then Item Claim Id must be specified.");

					writeData.Parameters.AddWithValue("@itemId", item.Id);
					writeData.Parameters.AddWithValue("@parentId", item.ParentId == Guid.Empty ? (object)DBNull.Value : item.ParentId);
					writeData.Parameters.AddWithValue("@externalId", !opt.IsSet(LoadOptions.ExternalId) || string.IsNullOrEmpty(item.ExternalId) ? DBNull.Value : (object)item.ExternalId);
					writeData.Parameters.AddWithValue("@securitySet", !opt.IsSet(LoadOptions.AccessClaims) || item.ItemClaimId == Guid.Empty ? DBNull.Value : (object)item.ItemClaimId);
					var parentPath = GetItemHref(item.ParentId, sqlConn);
					var href = parentPath == null ? "col://" + item.Name : parentPath;
					writeData.Parameters.AddWithValue("@href", href); // Should be able to get it directly

					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Builds a URI for a given item.
		/// </summary>
		/// <param name="id">id of the item to calculate the URI of</param>
		/// <param name="connection">database connection to use for the calculation.</param>
		/// <returns>A uri for the item or null if the item is not found.</returns>
		[Obsolete("This method should not be used and needs to be removed.  This query should be made inline with the parent SQL query that requires this value.")]
		private static string GetItemHref(Guid id, SqlConnection connection)
		{
			string href = String.Empty;
			using (SqlCommand command = new SqlCommand(Resources.ItemSelectNameSql, connection))
			{
				while (id != Guid.Empty)
				{
					command.Parameters.Clear();
					command.Parameters.AddWithValue("@id", id.ToString().ToUpperInvariant());
					var reader = command.ExecuteReader();
					if (reader.Read())
					{
						id = reader.IsDBNull(0) ? Guid.Empty : reader.GetGuid(0); // Getting the ParentId
						href = String.Format("{0}/{1}", reader.GetString(1), href);
					}
					reader.Close();
				}
			}
			if (!string.IsNullOrEmpty(href))
				return "col://" + href.TrimEnd('/');
			else
				return null;
		}

		/// <summary>
		/// Inserts the item.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="opt">The options of which fields should be set.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "These values are from a resouce file.")]
		private void UpdateItem(IItemInstance item, LoadOptions opt)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (opt == LoadOptions.None)
				throw new ArgumentException("LoadOptions cannot be set to none", "opt");

			using (SqlCommand writeData = new SqlCommand())
			{
				ArrayList columnUpdates = new ArrayList();

				if (opt.IsSet(LoadOptions.ParentId))
				{
					columnUpdates.Add("[ParentId] = @parentId");
					writeData.Parameters.AddWithValue("@parentId", item.ParentId == Guid.Empty ? (object)DBNull.Value : item.ParentId);
				}
				if (opt.IsSet(LoadOptions.ExternalId))
				{
					columnUpdates.Add("[ExternalId] = @externalId");
					writeData.Parameters.AddWithValue("@externalId", string.IsNullOrEmpty(item.ExternalId) ? DBNull.Value : (object)item.ExternalId);
				}
				if (opt.IsSet(LoadOptions.Version) && item.Published)
				{
					columnUpdates.Add("[PublishedVersion] = @publishedVersion");
					writeData.Parameters.AddWithValue("@publishedVersion", item.Version);
				}
				if (opt.IsSet(LoadOptions.ItemClaimId))
				{
					columnUpdates.Add("[ItemClaimId] = @securitySet");
					//writeData.Parameters.AddWithValue("@securitySet", item.ItemClaimId == Guid.Empty ? DBNull.Value : (object)item.ItemClaimId);
					writeData.Parameters.AddWithValue("@securitySet", !opt.IsSet(LoadOptions.AccessClaims) || item.ItemClaimId == Guid.Empty ? DBNull.Value : (object)item.ItemClaimId);
				}
				if (columnUpdates.Count < 1)
					return;
				string query = string.Format(CultureInfo.InvariantCulture, "{0} UPDATE structure.Item SET {2}  WHERE [Id] = @id; {1}", Resources.AdoNetItemPreRelationshipIntegritySql, Resources.AdoNetItemPostRelationshipIntegritySql, string.Join(", ", columnUpdates.Cast<string>().ToArray<string>()));
				writeData.Parameters.AddWithValue("@id", item.Id);
				writeData.CommandText = query;

				using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
				{
					sqlConn.Open();
					writeData.Connection = sqlConn;
					writeData.ExecuteNonQuery();
					sqlConn.Close();
				}
			}
		}

		/// <summary>
		/// Updates the item.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="changeCollection">The change collection.</param>
		/// <param name="requestType">Type of the request.</param>
		private void UpdateItemInstance(IItemInstance item, ItemChangeCollection changeCollection)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (changeCollection == null)
				throw new ArgumentNullException("changeCollection", "The change collection can not be null.");

			using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required))
			{
				if (item.Version == 0) // we are not creating a whole new item but we are making a new version
					CopyItemVersionAsNew(item);
				if (changeCollection.ChangeType.HasAnyType(ItemChangeTypes.Hierarchy | ItemChangeTypes.Version | ItemChangeTypes.PropertyAdd | ItemChangeTypes.PropertyDelete | ItemChangeTypes.PropertyModify))
				{
					UpdateItemVersion(item, changeCollection.Options);
					UpdateItem(item, changeCollection.Options);		// update item comes second incase the user is making a new version and publishing it a the same time.
				}
				if (changeCollection.Options.IsSet(LoadOptions.AccessClaims))
				{
					ProcessAccessClaimChanges(item, changeCollection);
				}
				if (changeCollection.Options.IsSet(LoadOptions.Relationships))
				{
					ProcessRelationshipChanges(item, changeCollection);
				}
				trans.Complete();
			}
		}

		/// <summary>
		/// Copies the item's leftversion into a new version and updates the item.Version number to reflect the newly created version.
		/// </summary>
		/// <param name="item">The item to copy the leftversion into a new version on.</param>
		private void CopyItemVersionAsNew(IItemInstance item)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (!item.LeftVersion.HasValue)
				throw new InvalidOperationException("LeftVersion refers to a source item which does not exist.  Can not derive a new version.");

			IItemInstance sourceItem = GetItem(item.Id, item.LeftVersion.Value, LoadOptions.ItemComplete ^ LoadOptions.AccessClaims);

			if (sourceItem == null)
				throw new InvalidOperationException("LeftVersion refers to a source item which does not exist.  Can not derive a new version.");

			sourceItem = sourceItem.ToStruct(); // convert to a value type
			sourceItem.Version = 0; // override our source item so that a new version is created.
			item.Version = InsertItemVersion(sourceItem, LoadOptions.ItemComplete);
			sourceItem.Version = item.Version;
			InsertItemRelationships(sourceItem, LoadOptions.ItemComplete);
			// no need to deal with the access claims since they are not version specific.
		}

		/// <summary>
		/// Processes the relationship changes.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="changeCollection">The change collection.</param>
		private void ProcessRelationshipChanges(IItemInstance item, ItemChangeCollection changeCollection)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (changeCollection == null)
				throw new ArgumentNullException("changeCollection");

			foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.RelationshipAdd))
			{
				IItemRelationship rel = (IItemRelationship)change.CurrentValue;
				InsertItemRelationship(item.Id, item.Version, rel.RelationshipType, rel.TargetItem, rel.State);
			}
			foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.RelationshipDelete))
			{
				IItemRelationship rel = (IItemRelationship)change.PreviousValue;
				DeleteItemRelationship(item.Id, item.Version, rel.RelationshipType, rel.TargetItem);
			}
			foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.RelationshipModify))
			{
				IItemRelationship rel = (IItemRelationship)change.GetChangeSource(item);
				UpdateItemRelationship(item.Id, item.Version, rel.RelationshipType, rel.TargetItem, rel.State);
			}
		}

		/// <summary>
		/// Processes the access claim changes.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="changeCollection">The change collection.</param>
		private void ProcessAccessClaimChanges(IItemInstance item, ItemChangeCollection changeCollection)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (changeCollection == null)
				throw new ArgumentNullException("changeCollection", "The change collection can not be null.");

			foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.PermissionAdd))
			{
				IItemClaim claim = (IItemClaim)change.CurrentValue;
				InsertItemClaim(claim.Id, claim.ClaimType, claim.ClaimValue, claim.Rights);
			}

			foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.PermissionDelete))
			{
				IItemClaim claim = (IItemClaim)change.PreviousValue;
				DeleteItemClaimBy3Keys(claim.Id, claim.ClaimType, claim.ClaimValue);
			}

			foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.PermissionModify))
			{
				IItemClaim claim = (IItemClaim)change.GetChangeSource(item);
				UpdateItemClaim(claim.Id, claim.ClaimType, claim.ClaimValue, claim.Rights);
			}
		}

		/// <summary>
		/// Deletes the item.
		/// </summary>
		/// <param name="id">The id.</param>
		private void DeleteItem(Guid id)
		{
			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id", "Id cannot be null");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(String.Format(CultureInfo.InvariantCulture, "DELETE FROM structure.Item WHERE Id = @Id"), sqlConn))
				{
					writeData.Parameters.AddWithValue("@Id", id);
					writeData.ExecuteNonQuery();
				}
			}
		}
		#endregion

		#region "ItemVersion"

		/// <summary>
		/// Inserts the item version.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="options">The options of which fields to set.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private int InsertItemVersion(IItemInstance item, LoadOptions options)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (options == LoadOptions.None)
				throw new ArgumentException("LoadOptions cannot be set to none", "options");

			int insertedVersion = 0;
			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemInsertItemVersionSql, sqlConn))
				{
					writeData.Parameters.AddWithValue("@itemId", item.Id);
					SqlParameter itemVersion = new SqlParameter("@version", SqlDbType.Int);
					itemVersion.Direction = ParameterDirection.InputOutput;
					itemVersion.Value = item.Version;
					writeData.Parameters.Add(itemVersion);
					writeData.Parameters.AddWithValue("@leftVersion", !options.IsSet(LoadOptions.LeftVersion) || item.LeftVersion == null ? (object)DBNull.Value : item.LeftVersion);
					writeData.Parameters.AddWithValue("@rightVersion", !options.IsSet(LoadOptions.RightVersion) || item.RightVersion == null ? (object)DBNull.Value : item.RightVersion);
					writeData.Parameters.AddWithValue("@comment", !options.IsSet(LoadOptions.Comment) || string.IsNullOrEmpty(item.Comment) ? (object)DBNull.Value : item.Comment);
					writeData.Parameters.AddWithValue("@name", item.Name);
					writeData.Parameters.AddWithValue("@mimeType", item.MimeType);
					writeData.Parameters.AddWithValue("@providerTypeId", item.Specification);
					writeData.Parameters.AddWithValue("@createdBy", item.CreatedBy);
					writeData.Parameters.AddWithValue("@lastModified", !options.IsSet(LoadOptions.LastModified) ? DateTime.Now : item.LastModified);
					writeData.Parameters.AddWithValue("@propertyData", SerializePropertyData(item.Properties));
					writeData.ExecuteNonQuery();
					insertedVersion = (int)itemVersion.Value;
				}
			}
			return insertedVersion;
		}

		/// <summary>
		/// Serializes the property data.
		/// </summary>
		/// <param name="properties">The properties.</param>
		/// <returns>a binary serialized List of ItemProperty</returns>
		static byte[] SerializePropertyData(IEnumerable<IItemProperty> properties)
		{
			List<ItemPropertySurrogate> s = new List<ItemPropertySurrogate>();
			foreach (IItemProperty p in properties)
			{
				s.Add(new ItemPropertySurrogate() { KeyName = p.KeyName, KeyValue = p.KeyValue });
			}
			using (MemoryStream ms = new MemoryStream())
			{
				ProtoBuf.Serializer.Serialize(ms, s);
				return ms.GetBuffer();
			}
			//IFormatter formatter = new BinaryFormatter();
			//using (MemoryStream ms = new MemoryStream())
			//{
			//     formatter.Serialize(ms, properties.ToStruct().ToList());
			//     return ms.GetBuffer();
			//}
		}

		/// <summary>
		/// Updates the item version.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="options">The options of which fields to set.</param>
		void UpdateItemVersion(IItemInstance item, LoadOptions options)
		{
			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null");

			if (options == LoadOptions.None)
				throw new ArgumentException("LoadOptions cannot be set to none", "options");

			using (SqlCommand writeData = new SqlCommand())
			{
				ArrayList columnUpdates = new ArrayList();

				if (options.IsSet(LoadOptions.LeftVersion))
				{
					columnUpdates.Add("[LeftVersion] = @leftVersion");
					writeData.Parameters.AddWithValue("@leftVersion", item.LeftVersion == null ? (object)DBNull.Value : item.LeftVersion);
				}
				if (options.IsSet(LoadOptions.RightVersion))
				{
					columnUpdates.Add("[RightVersion] = @rightVersion");
					writeData.Parameters.AddWithValue("@rightVersion", item.RightVersion == null ? (object)DBNull.Value : item.RightVersion);
				}
				if (options.IsSet(LoadOptions.Comment))
				{
					columnUpdates.Add("[Comment] = @comment");
					writeData.Parameters.AddWithValue("@comment", string.IsNullOrEmpty(item.Comment) ? (object)DBNull.Value : item.Comment);
				}
				if (options.IsSet(LoadOptions.Name))
				{
					columnUpdates.Add("[Name] = @name");
					writeData.Parameters.AddWithValue("@name", item.Name);
				}
				if (options.IsSet(LoadOptions.MimeType))
				{
					columnUpdates.Add("[MimeType] = @mimeType");
					writeData.Parameters.AddWithValue("@mimeType", item.MimeType);
				}
				if (options.IsSet(LoadOptions.Specification))
				{
					columnUpdates.Add("[SpecificationId] =@providerTypeId");
					writeData.Parameters.AddWithValue("@providerTypeId", item.Specification);
				}
				if (options.IsSet(LoadOptions.CreatedBy))
				{
					columnUpdates.Add("[CreatedBy] =@createdBy");
					writeData.Parameters.AddWithValue("@createdBy", item.CreatedBy);
				}
				if (options.IsSet(LoadOptions.Properties))
				{
					columnUpdates.Add("[PropertyData] =@propdata");
					writeData.Parameters.AddWithValue("@propdata", SerializePropertyData(item.Properties));
				}

				if (columnUpdates.Count < 1 && options.IsNotSet(LoadOptions.LastModified))
					return;
				columnUpdates.Add("[LastModified] = @lastModified");

				var lMod = !options.IsSet(LoadOptions.LastModified) ? DateTime.Now : item.LastModified;
				item.LastModified = lMod; // push back so the returned saved item is correct.
				writeData.Parameters.AddWithValue("@lastModified", lMod);

				string query = string.Format(CultureInfo.InvariantCulture, "UPDATE structure.ItemVersion SET {0}  WHERE [ItemId] = @itemId AND [Version] = @version", string.Join(", ", columnUpdates.Cast<string>().ToArray()));
				writeData.Parameters.AddWithValue("@itemId", item.Id);
				writeData.Parameters.AddWithValue("@version", item.Version);
				writeData.CommandText = query;

				using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
				{
					sqlConn.Open();
					writeData.Connection = sqlConn;
					writeData.ExecuteNonQuery();
					sqlConn.Close();
				}
			}
		}

		/// <summary>
		/// Deletes the item version.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <param name="version">The version.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "This value is from a resouce file.")]
		private void DeleteItemVersion(Guid id, int version)
		{
			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id", "Item Id cannot be null");

			if (version < 1)
				throw new ArgumentNullException("version");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand(Resources.AdoNetItemDeleteItemVersionSql, sqlConn))
				{
					writeData.Parameters.AddWithValue("@ItemId", id);
					writeData.Parameters.AddWithValue("@Version", version);
					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Deletes the item version.
		/// </summary>
		/// <param name="id">The id.</param>
		private void DeleteItemVersions(Guid id)
		{
			if (id == Guid.Empty || id == null)
				throw new ArgumentNullException("id", "Item Id cannot be null");

			using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
			{
				sqlConn.Open();
				using (SqlCommand writeData = new SqlCommand("DELETE FROM structure.ItemResource WHERE ItemId = @ItemId;DELETE FROM structure.ItemVersion WHERE ItemId = @ItemId", sqlConn))
				{
					writeData.Parameters.AddWithValue("@ItemId", id);
					writeData.ExecuteNonQuery();
				}
			}
		}

		#endregion


		/// <summary>
		/// Gets the columns to select given a set of load options.
		/// </summary>
		/// <param name="options">The options.</param>
		/// <returns></returns>
		private static string GetSelectColumns(LoadOptions options)
		{
			if (options == LoadOptions.None)
				throw new ArgumentException("LoadOptions cannot be set to none", "options");

			LoadOptions columns = options;
			// clear out all of the options that do not map to columns.
			string colNames = columns.Clear(LoadOptions.Properties | LoadOptions.Relationships | LoadOptions.AccessClaims | LoadOptions.Published | LoadOptions.Specification | LoadOptions.ItemClaimId).ToString();

			// if they want published that is a special calculated column
			if (options.IsSet(LoadOptions.Published))
				colNames = String.Join(", ", new string[] { colNames, "CAST(CASE WHEN Item.PublishedVersion = ItemVersion.Version THEN 1 ELSE 0 END AS bit) as Published" });

			if (options.IsSet(LoadOptions.Specification))
				colNames = String.Join(", ", new string[] { colNames, "SpecificationId" });

			if (options.IsSet(LoadOptions.Properties))
				colNames = String.Join(", ", new string[] { colNames, "PropertyData" });

			if (options.IsSet(LoadOptions.ItemClaimId))
				colNames = String.Join(", ", new string[] { colNames, "ItemClaimId" });

			return colNames;
		}

		/// <summary>
		/// Maps the item properties to the values in the data reader.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="item">The item.</param>
		/// <param name="i">The i.</param>
		private static void MapItemProperty(SqlDataReader reader, IItemInstance item, int i)
		{
			if (!reader.IsDBNull(i))
			{
				switch (reader.GetName(i))
				{
					case "Id": item.Id = reader.GetGuid(i); break;
					case "Comment": item.Comment = reader.GetString(i); break;
					case "CreatedBy": item.CreatedBy = reader.GetString(i); break;
					case "ExternalId": item.ExternalId = reader.GetString(i); break;
					case "LastModified": item.LastModified = reader.GetDateTime(i); break;
					case "LeftVersion": item.LeftVersion = reader.GetInt32(i); break;
					case "MimeType": item.MimeType = reader.GetString(i); break;
					case "Name": item.Name = reader.GetString(i); break;
					case "ParentId": item.ParentId = reader.GetGuid(i); break;
					case "Published": item.Published = reader.GetBoolean(i); break;
					case "SpecificationId": item.Specification = reader.GetGuid(i); break;
					case "RightVersion": item.RightVersion = reader.GetInt32(i); break;
					case "Version": item.Version = reader.GetInt32(i); break;
					case "Href": item.Href = new Uri(reader.GetString(i)); break;
					case "ItemClaimId": item.ItemClaimId = reader.GetGuid(i); break;
					case "PropertyData": item.Properties = DeserializeProperties((byte[])reader.GetValue(i)); break;
				}
			}
		}

		/// <summary>
		/// Deserializes the properties.
		/// </summary>
		/// <param name="buffer">The buffer.</param>
		/// <returns>a list of item properties</returns>
		private static IEnumerable<MetaCollection.Framework.Model.IItemProperty> DeserializeProperties(byte[] buffer)
		{
			if (buffer == null || buffer.Length == 0)
				return null;
			List<ItemPropertySurrogate> props;
			using (var ms = new MemoryStream(buffer))
			{
				props = (List<ItemPropertySurrogate>)ProtoBuf.Serializer.Deserialize<List<ItemPropertySurrogate>>(ms);

				IEnumerable<IItemProperty> result = null;
				if (props != null)
				{
					result = props.Select(p => (IItemProperty)p);
				}
				return result;
			}
		}
	}

	public class AdoItemInstance : IItemInstance
	{
		#region IItemInstance Members

		public Guid Id { get; set; }

		public string ExternalId { get; set; }

		public Guid ParentId { get; set; }

		public int Version { get; set; }

		public int? LeftVersion { get; set; }

		public int? RightVersion { get; set; }

		public string CreatedBy { get; set; }

		public string Comment { get; set; }

		public string Name { get; set; }

		public bool Published { get; set; }

		public string MimeType { get; set; }

		public Guid Specification { get; set; }

		public DateTime LastModified { get; set; }

		public Uri Href { get; set; }

		public Guid ItemClaimId { get; set; }

		public IEnumerable<IItemProperty> Properties { get; set; }

		public IEnumerable<IItemClaim> AccessClaims { get; set; }

		public IEnumerable<IItemRelationship> Relationships { get; set; }

		#endregion
	}


}
